/**
 * 
 */
package com.hs.chd.dwr;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.directwebremoting.WebContext;
import org.directwebremoting.WebContextFactory;
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

import com.hs.auth.model.HsAccount;
import com.hs.chd.catagory.SubjectUtil;
import com.hs.chd.helper.DataImpObservable;
import com.hs.chd.helper.DataImpObserver;
import com.hs.chd.helper.ExcelHelper;
import com.hs.chd.model.ChdPaperinfo;
import com.hs.chd.service.IDataService;
import com.hs.core.db.DBMgr;
import com.hs.core.dwr.DwrHelper;
import com.hs.core.imp.ExcelImportUtil;
import com.hs.core.service.IService;
import com.hs.core.utils.TimeUtil;

/**
 * @author Administrator
 *
 */
public class DataImpHelper extends DwrHelper{
	/**
	 * 数据导入执行查询
	 * @param fileId
	 * @return
	 */
    public String queryByFileId(String fileId){
     	DBMgr dbo = this.getDbo(this.getHttpServletRequest());
    	String currnum = "0";
    	String allnum = "0";
    	String status = "0";
    	String failnum = "0";
    	List filelist = dbo.queryList("select * from CHD_IMPFILEINFO where id =?", new Object[]{fileId});
    	if(filelist.size() > 0){
    		 Map filedata = (Map)filelist.get(0);
    		 currnum =  filedata.get("IMPCURRNUM")+"";
    		 allnum =  filedata.get("IMPNUM")+"";
    		 status =  filedata.get("IMPSTATUS")+"";
    		 failnum =  filedata.get("IMPFAILPOS")+"";
    	}
    	System.out.println("IMPCURRNUM:" + currnum);
 
    	return currnum+"#"+fileId+"#" + allnum+"#"+status+"#"+failnum;
    }
    
    /**
	 * 删除数据及已导入的数据
	 * @param fileId
	 * @return
	 */
    public String delByFileId(String fileId){
    	HttpServletRequest  request = this.getHttpServletRequest();
    	DBMgr dbo = this.getDbo(this.getHttpServletRequest()); 
    	if(null == fileId || "null".equalsIgnoreCase(fileId)){
    		return "";
    	} 
    	dbo.update("delete from CHD_PAPERINFO where impfileid=?", new Object[]{fileId}); 
    	dbo.update("update  CHD_IMPFILEINFO set ISDEL =0 where id=?", new Object[]{fileId});
    	return "";
    }
	/**
	 * 数据导入
	 * @param fileId
	 * @return
	 */
    public String impData(String fileId,String isdoublecheck){
    	HttpServletRequest  request = this.getHttpServletRequest();
    	DBMgr dbo = this.getDbo(this.getHttpServletRequest());
    	String filepath = "";
    	String datatype = "0";
    	long impcurrnum = 0;
    	List filelist = dbo.queryList("select FILEPATH,DATATYPE,IMPCURRNUM from CHD_IMPFILEINFO where id =?", new Object[]{fileId});
    	if(filelist.size() > 0){
    		 Map filedata = (Map)filelist.get(0);
    		 filepath = (String)filedata.get("FILEPATH");
    		 datatype =  filedata.get("DATATYPE")+"";
    		 
    		 BigDecimal cobj =  (java.math.BigDecimal)filedata.get("IMPCURRNUM");
    		 if(null != cobj)
    		       impcurrnum = cobj.longValue();
    	}
    	dbo.update("update CHD_IMPFILEINFO set IMPSTATUS=2 where id=?", new Object[]{fileId});
    	System.out.println("FILEPATH:" + filepath);
    	if("1".equals(datatype)){
    		magaDataimp(fileId,filepath,isdoublecheck,request,impcurrnum);
    	}else if("2".equals(datatype)){
    		oldDataimp(fileId,filepath,isdoublecheck,request,impcurrnum);
    	}  
    	return "";
    }
    /**
     * 现代期刊数据导入
     * @param filepath
     * @param isdoublecheck
     * @param request
     */
    private String magaDataimp(String fileid,String filepath,String isdoublecheck,HttpServletRequest  request,long impcurrnum){
    	 IDataService dataService  = (IDataService)this.getServiceInst("dataService");
    	 ExcelImportUtil inst = ExcelImportUtil.getInstance(new File(filepath));
		 HSSFWorkbook workbook = null;
		 int impCreateSuccessSize = 0;
	     int impUpdateSuccessSize = 0; 
	     //当前导入数量
	     int currentnum = 0;
	     //总数量
	     int allnum = 0;
	 	 HsAccount impUser =this.getCurrentuser(request);
		 try {
				workbook = inst.readFile();
				int total = workbook.getNumberOfSheets(); //获取sheet个数      
		        int start = 0;
		        int end = 0 ;   
		        SubjectUtil util = SubjectUtil.getInst();
		        //获取sheet个数
				for(int i = 0 ; i < 1 ; i ++){  
		            HSSFSheet sheet = workbook.getSheetAt(i);// 按索引获取sheet引用   
		            start = sheet.getFirstRowNum();  //sheet起始行索引      
		            end = sheet.getLastRowNum();//sheet起结束行索引      
		            int counter = 1;   
		            allnum = end;
		            HSSFRow row = null ; 
		            HSSFCell classno = null ; 
		        	HSSFCell title = null ;  
		        	HSSFCell author = null ;    
		        	HSSFCell authorunit = null ;
		        	HSSFCell areastr = null ; 
		        	HSSFCell cwnum = null ;
		        	
		        	HSSFCell periodicalname = null ;  
		        	HSSFCell yearstr = null ;
		        	
		        	HSSFCell volume = null ;   
		        	HSSFCell period = null ; 
		        	HSSFCell pageno = null ; 
		        	HSSFCell issnstr = null ; 
		        	HSSFCell contrycode = null ; 
		        	HSSFCell codestr = null ;
		        	HSSFCell publisharea = null ;
		        	
		        	HSSFCell keyword = null ;  
		        	HSSFCell subjectword = null ; 
		        	HSSFCell impressword = null ; 
		        	HSSFCell updatedate = null ; 
		        	HSSFCell titlen = null ;   
		        	HSSFCell abstract_ = null ;  
		        	HSSFCell authorabstract = null ; 
		        	HSSFCell documenttype = null ;  
		        	HSSFCell fundstr = null ; 
		        	HSSFCell language = null ;   
		        	HSSFCell usersubject = null ; 
		        	HSSFCell castr = null ;   

		        	DataImpObservable dataimp = new DataImpObservable();
					DataImpObserver watcher = new DataImpObserver();
					dataimp.addObserver(watcher);
		            while(counter <= end){      
		            	 ChdPaperinfo maga = null;
		                 row =  sheet.getRow(counter);//获取第N行表格数据     
		                 if(row != null){
		                	  counter ++ ;
		                	  currentnum = counter;
		                	  classno = row.getCell(0);//获取指定列单元格            
		                	  title = row.getCell(1);   
		                	  author = row.getCell(2);
		                	  authorunit = row.getCell(3);
		                	  areastr = row.getCell(4);
		                	  cwnum = row.getCell(5);
		                	  periodicalname = row.getCell(6);
		                	  yearstr = row.getCell(7);
		                	  volume = row.getCell(8);
		                	  period = row.getCell(9);
		                	  pageno = row.getCell(10);
		                	  issnstr = row.getCell(11); 
		  		        	  contrycode = row.getCell(12);
		  		        	  codestr = row.getCell(13);
		  		        	  publisharea = row.getCell(14);
		  		        	
		  		        	  keyword = row.getCell(15); 
		  		        	  subjectword = row.getCell(16); 
		  		        	  impressword = row.getCell(17);
		  		        	  updatedate = row.getCell(18); 
		  		        	  titlen = row.getCell(19);  
		  		        	  abstract_ = row.getCell(20);  
		  		        	  authorabstract = row.getCell(21);
		  		        	  documenttype = row.getCell(22); 
		  		        	  fundstr = row.getCell(23);
		  		        	  language = row.getCell(24);  
		  		        	  usersubject = row.getCell(25); 
		  		        	  castr = row.getCell(26);   
		  		        	 //判断该文件是否已经部分导入
								if(impcurrnum > 0){
									//导入数量大于0 则修改查重规则默认为查重
									isdoublecheck = "1";
								}
		  		        	  //是否查重
		  		        	 if("1".equals(isdoublecheck)){
			  		        	 String titlestr = getCell(title);
				                 String periodicalnamestr = getCell(periodicalname);
			  		        	 String yearstr1 = getCell(yearstr);
				                 String volumestr = getCell(volume);
				                 String periodstr = getCell(period);
			  		        	 StringBuffer countsql = new StringBuffer();
				                 countsql.append("select id from CHD_PAPERINFO ");
				                 countsql.append(" where title='");
				                 countsql.append(titlestr==null?null:titlestr.trim().replaceAll("'", "''")); 
				                 countsql.append("' and periodicalname='");
				                 countsql.append(periodicalnamestr==null?null:periodicalnamestr.trim().replaceAll("'", "''") + "'");
				                 
				                 if(StringUtils.isNotEmpty(yearstr1)){
				                	 countsql.append(" and yearstr='" + yearstr1.trim() + "'");
				                 } 
				                 if(StringUtils.isNotEmpty(periodstr)){
				                	 countsql.append(" and period='" + periodstr.trim() + "'");
				                 }
				                
								 List accList = DBMgr.getDbUtilInst().queryList(countsql.toString(),null);  
								 int acclen = accList.size();
								 if(acclen >0){
									 Map data = (Map)accList.get(0);
								     String id = data.get("id") + "";
								     maga = (ChdPaperinfo)dataService.findByID(ChdPaperinfo.class, new Long(id));
								     impUpdateSuccessSize++;
								 }else{
									 maga = new ChdPaperinfo();
									 impCreateSuccessSize++;
								 }
		  		        	 }else if("2".equals(isdoublecheck)){
		  		        		 maga = new ChdPaperinfo();
		  		        		 impCreateSuccessSize++;
		  		        	 }
							 
				                 if(null != classno){
				                	 maga.setClassno(getCell(classno).trim()); 
				                 }else{
				                	 maga.setClassno("");
				                 }
				                 if(null != title){
				                	 maga.setTitle(getCell(title).trim()); 
				                 }else{
				                	 maga.setTitle("");
				                 }
				                 if(null != author){
				                	 maga.setAuthor(getCell(author).trim()); 
				                 }else{
				                	 maga.setAuthor("");
				                 }
				                 if(null != authorunit){
				                	 maga.setAuthorunit(getCell(authorunit).trim()); 
				                 }else{
				                	 maga.setAuthorunit("");
				                 }
				                 if(null != areastr){
				                	 maga.setAreastr(getCell(areastr).trim()); 
				                 }else{
				                	 maga.setAreastr("");
				                 }
				                 if(null != cwnum){
				                	 maga.setCwnum(getCell(cwnum).trim()); 
				                 }else{
				                	 maga.setCwnum("");
				                 }
				                 if(null != periodicalname){
				                	 maga.setPeriodicalname(getCell(periodicalname).trim()); 
				                 }else{
				                	 maga.setPeriodicalname("");
				                 }
				                 if(null != yearstr){
				                	 maga.setYearstr(getCell(yearstr).trim()); 
				                 }else{
				                	 maga.setYearstr("");
				                 }
				                 if(null != volume){
				                	 maga.setVolume(getCell(volume).trim()); 
				                 }else{
				                	 maga.setVolume("");
				                 }
				                 if(null != period){
				                	 maga.setPeriod(getCell(period).trim()); 
				                 }else{
				                	 maga.setPeriod("");
				                 }
				                 if(null != pageno){
				                	 maga.setPageno(getCell(pageno).trim()); 
				                 }else{
				                	 maga.setPageno("");
				                 }
				                 if(null != issnstr){
				                	 maga.setIssnstr(getCell(issnstr).trim()); 
				                 }else{
				                	 maga.setIssnstr("");
				                 }
				                 if(null != contrycode){
				                	 maga.setContrycode(getCell(contrycode).trim()); 
				                 }else{
				                	 maga.setContrycode("");
				                 }
				                 if(null != codestr){
				                	 maga.setCodestr(getCell(codestr).trim()); 
				                 }else{
				                	 maga.setCodestr("");
				                 }
				                 if(null != publisharea){
				                	 maga.setPublisharea(getCell(publisharea).trim()); 
				                 }else{
				                	 maga.setPublisharea("");
				                 }
				                 if(null != keyword){
				                	 maga.setKeyword(getCell(keyword).trim()); 
				                 }else{
				                	 maga.setKeyword("");
				                 }
				                 if(null != subjectword){
				                	 maga.setSubjectword(getCell(subjectword).trim()); 
				                 }else{
				                	 maga.setSubjectword("");
				                 }
				                 if(null != impressword){
				                	 maga.setImpressword(getCell(impressword).trim()); 
				                 }else{
				                	 maga.setImpressword("");
				                 }
				                 if(null != updatedate){
				                	 maga.setUpdatedate(getCell(updatedate).trim()); 
				                 }else{
				                	 maga.setUpdatedate("");
				                 }
				                 if(null != titlen){
				                	 maga.setTitlen(getCell(titlen).trim()); 
				                 }else{
				                	 maga.setTitlen("");
				                 }
				                 if(null != abstract_){
				                	 String temp = getCell(abstract_).trim();
				                	 String temp2 = temp;
 				                	 if(temp.length()>=4000)
				                		 temp2 = temp.substring(0,3990);
				                	 maga.setAbstract_(temp2); 
				                 }else{
				                	 maga.setAbstract_("");
				                 }
				                 if(null != authorabstract){
				                	 maga.setAuthorabstract(getCell(authorabstract).trim()); 
				                 }else{
				                	 maga.setAuthorabstract("");
				                 }
				                 if(null != documenttype){
				                	 maga.setDocumenttype(getCell(documenttype).trim()); 
				                 }else{
				                	 maga.setDocumenttype("");
				                 }
				                 if(null != fundstr){
				                	 maga.setFundstr(getCell(fundstr).trim()); 
				                 }else{
				                	 maga.setFundstr("");
				                 } 
				                 if(null != language){
				                	 maga.setLanguage(getCell(language).trim()); 
				                 }else{
				                	 maga.setLanguage("");
				                 }  
				                 if(null != usersubject){
				                	 maga.setUsersubject(getCell(usersubject).trim()); 
				                 }else{
				                	 maga.setUsersubject("");
				                 }  
				                 if(null != castr){
				                	 maga.setCastr(getCell(castr).trim()); 
				                 }else{
				                	 maga.setCastr("");
				                 } 
							     if("eng".equals(maga.getLanguage())){
							    	 maga.setDatatype(new Long(2));
							     }else{
							    	 maga.setDatatype(new Long(1));
							     }
						    	 maga.setImpdate(TimeUtil.getCurrentDate());
						    	 maga.setIsimg(new Long(0));
						    	 maga.setImpfileid(new Long(fileid));
						    	 maga.setImpuser(impUser == null?0l:impUser.getId());
						    	 dataService.saveObject(maga);
								 dataimp.countImpNum(new Long(impCreateSuccessSize+impUpdateSuccessSize),new Long(allnum),new Long(fileid));
		                 }
		            } 
				}
			return ":" + impCreateSuccessSize + ":" + impUpdateSuccessSize;
		} catch (Exception e) {
			System.err .println("currentnum:" + currentnum + "::" +e.getMessage());
			DBMgr dbo = this.getDbo(this.getHttpServletRequest()); 
	    	dbo.update("update CHD_IMPFILEINFO set IMPSTATUS=3,IMPFAILPOS=? where id=?", new Object[]{currentnum,fileid});
		}
		return ":0:0";
    }
    /**
     * 古籍数据导入
     * @param filepath
     * @param isdoublecheck
     * @param request
     */
    private String oldDataimp(String fileid,String filepath,String isdoublecheck,HttpServletRequest  request,Long impcurrnum){
    	 IDataService dataService  = (IDataService)this.getServiceInst("dataService");
    	 
		 ExcelImportUtil inst = ExcelImportUtil.getInstance(new File(filepath));
		 HSSFWorkbook workbook = null;
		 int impCreateSuccessSize = 0;
	     int impUpdateSuccessSize = 0; 
	     //当前导入数量
	     int currentnum = 0;
	     //总数量
	     int allnum = 0;
	 	 HsAccount impUser =this.getCurrentuser(request);
		 try {
				workbook = inst.readFile();
				int total = workbook.getNumberOfSheets(); //获取sheet个数      
		        int start = 0;
		        int end = 0 ;   
		        //获取sheet个数
				for(int i = 0 ; i < 1 ; i ++){  
		            HSSFSheet sheet = workbook.getSheetAt(i);// 按索引获取sheet引用   
		            start = sheet.getFirstRowNum();  //sheet起始行索引      
		            end = sheet.getLastRowNum();//sheet起结束行索引      
		            int counter = 1;      
		            HSSFRow row = null ; 
		            allnum = end ;
		        	HSSFCell bookname = null ;    
		        	HSSFCell tcmname = null ;   
		        	HSSFCell originaltext = null ;   
		        	HSSFCell volumesection = null ;   
		        	HSSFCell bookversion = null ; 
		        	HSSFCell inputuser = null ; 
		        	HSSFCell inputdate = null ;  
		        	HSSFCell chaodai = null ; 
		        	DataImpObservable dataimp = new DataImpObservable();
					DataImpObserver watcher = new DataImpObserver();
					dataimp.addObserver(watcher);
		            while(counter <= end){      
		            	 ChdPaperinfo maga = null; 
		                 row =  sheet.getRow(counter);//获取第N行表格数据     
		                 if(row != null){
		                	  counter ++ ; 
		                	  currentnum = counter;
		                	  bookname = row.getCell(1);
		                	  tcmname = row.getCell(2);
		                	  originaltext = row.getCell(3);
		                	  volumesection = row.getCell(4);
		                	  bookversion = row.getCell(5);
		                	  inputuser = row.getCell(6);
		                	  inputdate = row.getCell(7); 
		                	  chaodai = row.getCell(8); 
		                	  StringBuffer countsql = new StringBuffer();
				                 countsql.append("select id from CHD_PAPERINFO ");
				                 countsql.append(" where originaltext='");
				                 countsql.append(getCell(originaltext)==null?"":getCell(originaltext).trim().replaceAll("'", "''"));  
				                 countsql.append("'");
				                 String tcmname1 = getCell(tcmname);
				                 String juance = getCell(volumesection);  
				                 if(StringUtils.isNotEmpty(juance)){
				                	 countsql.append(" and VOLUMESECTION='");  
				                	 countsql.append(juance.trim().replaceAll("'", "''"));  
				                	 countsql.append("'");  
				                	 
				                 } 
			                    //判断该文件是否已经部分导入
								if(impcurrnum > 0){
									//导入数量大于0 则修改查重规则默认为查重
									isdoublecheck = "1";
								}
								if("1".equals(isdoublecheck)){
									 List accList = DBMgr.getDbUtilInst().queryList(countsql.toString(),null);  
									 int acclen = accList.size(); 
									 
								     if(acclen > 0){
								    	 Map data = (Map)accList.get(0);
									     String id = data.get("id") + "";
									     maga = (ChdPaperinfo)dataService.findByID(ChdPaperinfo.class, new Long(id));
									     impUpdateSuccessSize++; 
									 }else{
										 maga = new ChdPaperinfo();
										 impCreateSuccessSize ++;
									 }
								}else if("2".equals(isdoublecheck)){
									 maga = new ChdPaperinfo();
									 impCreateSuccessSize ++;
								}
		                	   
				                 if(null != bookname){
				                	 maga.setBookname(getCell(bookname).trim()); 
				                 }else{
				                	 maga.setBookname("");
				                 }
				                 if(null != tcmname){
				                	 maga.setTcmname(getCell(tcmname).trim()); 
				                 }else{
				                	 maga.setTcmname("");
				                 }
				                 if(null != originaltext){
				                	 maga.setOriginaltext(getCell(originaltext).trim()); 
				                 }else{
				                	 maga.setOriginaltext("");
				                 }
				                 if(null != volumesection){
				                	 maga.setVolumesection(getCell(volumesection).trim()); 
				                 }else{
				                	 maga.setVolumesection("");
				                 }
				                 if(null != bookversion){
				                	 maga.setBookversion(getCell(bookversion).trim()); 
				                 }else{
				                	 maga.setBookversion("");
				                 }
				                 if(null != inputuser){
				                	 maga.setInputuser(getCell(inputuser).trim()); 
				                 }else{
				                	 maga.setInputuser("");
				                 }
				                 if(null != inputdate){
				                	 maga.setInputdate(getCell(inputdate).trim()); 
				                 }else{
				                	 maga.setInputdate("");
				                 }
				                 if(null != chaodai){
				                	 maga.setBookcd(getCell(chaodai).trim()); 
				                 }else{
				                	 maga.setBookcd("");
				                 } 
							     maga.setDatatype(new Long(3));
						    	 maga.setImpdate(TimeUtil.getCurrentDate());
						    	 maga.setIsimg(new Long(0));
						    	 maga.setImpuser(impUser==null?0l:impUser.getId());
						    	 maga.setImpfileid(new Long(fileid));
						    	 dataService.saveObject(maga);
								 dataimp.countImpNum(new Long(impCreateSuccessSize+impUpdateSuccessSize),new Long(allnum),new Long(fileid));
		                 } 
		            } 
				}
			return ":" + impCreateSuccessSize + ":" + impUpdateSuccessSize;
    	} catch (Exception e) {
			System.err .println("currentnum:" + currentnum + "::" +e.getMessage());
			DBMgr dbo = this.getDbo(this.getHttpServletRequest()); 
	    	dbo.update("update CHD_IMPFILEINFO set IMPSTATUS=3,IMPFAILPOS=? where id=?", new Object[]{currentnum,fileid});
		}
		return ":0:0";
    } 
    /**
	 * 获得request
	 * @return
	 */
	public HttpServletRequest getHttpServletRequest(){
		WebContext ctx = WebContextFactory.get();   
		return ctx.getHttpServletRequest();   
	}
	public static String getCell(HSSFCell cell) {
		if (cell == null)
			return "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:
			return (long) cell.getNumericCellValue() + "";
		case HSSFCell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		case HSSFCell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();
		case HSSFCell.CELL_TYPE_BLANK:
			return "";
		case HSSFCell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue() + "";
		case HSSFCell.CELL_TYPE_ERROR:
			return cell.getErrorCellValue() + "";
		default:
			return "";
		}
	}
	/**
	 * 获得session
	 * @return
	 */
	public HttpSession getHttpSession(){
		WebContext ctx = WebContextFactory.get();   
		return ctx.getSession();   
	}
	/**
	 * 
	 * @return
	 */
	public DBMgr getDbo(){
		DBMgr dbo = DBMgr.getDbUtilInst(getHttpSession().getServletContext());
		return dbo;
	} 
	public IService getServiceInst(String beanname){
	         WebApplicationContext wac = WebApplicationContextUtils.getRequiredWebApplicationContext(getHttpSession().getServletContext());
//	         String[] names = wac.getBeanDefinitionNames();
//	         for(int i = 0;i<names.length;i++){
//	        	  System.out.println("bean:" + names[i]);
//	         }
	         return (IService)wac.getBean(beanname);
	}
}
